package cn.ztydata.teachingsystem.heaven.dao;


import cn.ztydata.teachingsystem.common.Page;
import cn.ztydata.teachingsystem.exception.ServiceException;
import cn.ztydata.teachingsystem.heaven.cons.ExerciseStatus;
import cn.ztydata.teachingsystem.heaven.entity.exercise.Exercise;
import cn.ztydata.teachingsystem.heaven.entity.test.StudentAnswer;
import cn.ztydata.teachingsystem.heaven.entity.test.TestExercise;
import com.google.common.collect.Maps;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 习题库DAO类
 * Created by lyj on 2015/2/4.
 */
@Repository
public class ExerciseDao extends BaseDao {
    /**
     * 获取不同习题的总数(公开且已审核通过的习题)
     *
     * @author chenxin
     * @since 2015/2/4
     */
    public List<Map<String, Object>> findTotalByType() {
        String sql = "SELECT tiType AS type, COUNT(*) AS total FROM t_exercise where tiOpen=1 AND tiStatus=1 GROUP BY tiType";

        log.debug("获取不同习题的总数");
        return namedParameterJdbcTemplate.query(sql, new RowMapper<Map<String, Object>>() {
            @Override
            public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
                Map<String, Object> result = Maps.newHashMap();

                result.put("type", rs.getInt("type"));
                result.put("total", rs.getLong("total"));

                return result;
            }
        });
    }

    /**
     * 获取指定用户创建的不同类型习题的总数(不区分状态)
     * @param userId 创建人编号
     * @return List
     *
     * @author fanruiping
     * @since 2015-05-06
     */
    public List<Map<String, Object>> findTotalByTypeAndUser(Long userId) {
        String sql = "SELECT tiType AS type, COUNT(*) AS total FROM t_exercise WHERE iCreatorId=:userId GROUP BY tiType";

        return namedParameterJdbcTemplate.query(sql, new MapSqlParameterSource().addValue("userId",userId),new RowMapper<Map<String, Object>>() {
            @Override
            public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
                Map<String, Object> result = Maps.newHashMap();

                result.put("type", rs.getInt("type"));
                result.put("total", rs.getLong("total"));

                return result;
            }
        });
    }

    /**
     * 审核习题
     *
     * @param mapAudit
     * @return boolean
     * @author lyj
     * @since 2015-2-4
     */
    public int auditExercise(Map<String, Object> mapAudit) {

        String sql = "UPDATE t_exercise SET tiStatus=:status,dtAuditTime=NOW(),iAuditorId=:iAuditorId WHERE id=:id";

        MapSqlParameterSource parameterSource = new MapSqlParameterSource()
                .addValue("id", mapAudit.get("id"))
                .addValue("status", mapAudit.get("status"))
                .addValue("iAuditorId", mapAudit.get("auditorId"));

        log.debug("审核、删除习题");
        return namedParameterJdbcTemplate.update(sql, parameterSource);
    }


    /**
     * 统计习题类型习题数
     * @param deptId 系部ID
     * @param specId 专业ID
     * @param status 习题状态 （0：待审核 1：审核通过 2：驳回 3：删除 4：全部）
     * @param isOpen 是否公开 （0：不公开 1:公开）
     * @param creatorId 创建人ID
     * @return
     * @author lyj
     * @since 2015-2-28
     */
    public List<Map<String, Object>> getTypeExercises(long deptId, long specId, int status, int isOpen, long creatorId) {
        StringBuilder sql = new StringBuilder("SELECT tiType,COUNT(id) AS count FROM t_exercise WHERE iParentId=0");
        MapSqlParameterSource param = new MapSqlParameterSource();

        //根据部门过滤
        if (deptId > 0) {
            log.debug("根据部门过滤:deptId > 0");
            sql.append(" AND iDeptId = :iDeptId");
            param.addValue("iDeptId", deptId);
        }

        //根据专业过滤
        if (specId > 0) {
            log.debug("根据专业过滤:specId > 0");
            sql.append(" AND iSpecId = :iSpecId");
            param.addValue("iSpecId", specId);
        }

        //习题状态必须大于等于0
        if (status >= 0 && status != ExerciseStatus.EXERCISE_ALL) {
            log.debug("习题状态必须大于等于0:status >= 0 && status != ExerciseStatus.EXERCISE_ALL");
            //当status等于4时，查询出习题状态为“待审核”、“审核通过”、“审核驳回”的所有习题
            sql.append(" AND tiStatus = :tiStatus");
            param.addValue("tiStatus", status);
        }

        //过滤共有私有
        if (isOpen >= 0 && isOpen != 2) {
            log.debug("过滤共有私有:isOpen >= 0 && isOpen != 2");
            sql.append(" AND tiOpen = :tiOpen");
            param.addValue("tiOpen", isOpen);
        }

        //根据用户ID过滤
        if (creatorId > 0) {
            log.debug("根据用户ID过滤:creatorId > 0");
            sql.append(" AND iCreatorId = :iCreatorId");
            param.addValue("iCreatorId", creatorId);
        }

        sql.append(" GROUP BY tiType ");

        log.debug("统计习题类型习题数");
        return namedParameterJdbcTemplate.query(sql.toString(), param, new RowMapper<Map<String, Object>>() {
            @Override
            public Map<String, Object> mapRow(ResultSet resultSet, int i) throws SQLException {
                Map<String, Object> map = new HashMap<>();

                map.put("id", resultSet.getInt("tiType"));
                map.put("count", resultSet.getInt("count"));
                return map;
            }
        });
    }


    /**
     * 习题列表
     * @param pageNum 当前页码
     * @param pageSize 每页条数
     * @param deptIds 部门ＩＤ
     * @param specIds　专业ＩＤ集合
     * @param creatorId　创建人
     * @param status 习题状态（0：未审核 1：审核通过 2：审核驳回 3：删除 4：全部）
     * @param isOpen 公开私有（0：私有 1：公开 2：全部）
     * @param search　关键字
     * @param types　类型集合
     * @return
     * @author lyj
     * @since 2015-2-7
     */

    public Page<Exercise> getExerciseList(int pageNum, int pageSize, List<Long> deptIds, List<Long> specIds, long creatorId, int status, int isOpen, String search, List<Long> types, int tag){

        StringBuilder sql = new StringBuilder("SELECT ex.id, ex.tiType, ex.sShowTopic, ex.tiDifficulty, ex.iSpecId, ex.tiStatus, t.sName FROM t_exercise ex JOIN t_teacher t ON ex.iCreatorId = t.id");
        StringBuilder sqlCount = new StringBuilder("SELECT COUNT(ex.id) FROM t_exercise ex JOIN t_teacher t ON ex.iCreatorId = t.id");

        StringBuilder where = new StringBuilder(" WHERE ex.iParentId = 0");

        MapSqlParameterSource param = new MapSqlParameterSource();

        //根据部门过滤
        if (deptIds.size() > 0){
            log.debug("根据部门过滤:deptIds.size() > 0");
            where.append(" AND ex.iDeptId IN (:iDeptId)");
            param.addValue("iDeptId", deptIds);
        }

        //根据专业过滤
        if (specIds.size() > 0 ){
            log.debug("根据专业过滤:specIds.size() > 0");
            where.append(" AND ex.iSpecId IN (:iSpecId)");
            param.addValue("iSpecId", specIds);
        }

        //根据用户ID过滤
        if (creatorId > 0){
            log.debug("根据用户ID过滤:creatorId > 0");
            if (tag == 3){
                log.debug("如果tag=3：tag == 3");
                where.append(" AND ex.iAuditorId = :iAuditorId");
                param.addValue("iAuditorId", creatorId);
            } else {
                log.debug("如果tag!=3");
                where.append(" AND ex.iCreatorId = :iCreatorId");
                param.addValue("iCreatorId", creatorId);
            }
        }

        //习题状态必须大于等于0
        if (status >= 0 && status != ExerciseStatus.EXERCISE_ALL) {
            log.debug("习题状态必须大于等于0:status >= 0 && status != ExerciseStatus.EXERCISE_ALL");
            //当status等于4时，查询出习题状态为“待审核”、“审核通过”、“审核驳回”的所有习题
            where.append(" AND ex.tiStatus = :tiStatus");
            param.addValue("tiStatus", status);
        }

        //过滤共有私有
        if (isOpen >= 0 && isOpen != 2) {
            log.debug("过滤共有私有:isOpen >= 0 && isOpen != 2");
            where.append(" AND ex.tiOpen = :tiOpen");
            param.addValue("tiOpen", isOpen);
        }

        //根据关键字模糊搜索
        if (!search.isEmpty()){
            log.debug("根据关键字模糊搜索:!search.isEmpty()");
            where.append(" AND UPPER(ex.sTopic) LIKE :search");
            param.addValue("search", "%" + search.toUpperCase() + "%");
        }

        //根据习题类型过滤习题
        if (!(types == null || types.size() == 0)) {
            log.debug("根据习题类型过滤习题:!(types == null || types.size() == 0)");
            where.append(" AND ex.tiType IN (:tiType)");
            param.addValue("tiType", types);
        }

        //获取查询总条数
        int total = namedParameterJdbcTemplate.queryForObject(sqlCount.append(where).toString(), param, Integer.class);

        //获取总条数
        if (total == 0) {
            log.debug("获取总条数为零：total == 0");
            return new Page<>();
        }
        where.append(" ORDER BY ex.dtCreationTime DESC");

        //追加limit限制
        where.append(getLimitStr(pageNum, pageSize));

        //获取习题列表（习题ID，题干，习题类型，难度）
        List<Exercise> exerciseList = namedParameterJdbcTemplate.query(sql.append(where).toString(), param, new RowMapper<Exercise>() {
            @Override
            public Exercise mapRow(ResultSet resultSet, int i) throws SQLException {
                Exercise exercise = new Exercise();

                exercise.setId(resultSet.getLong("id"));
                exercise.setType(resultSet.getInt("tiType"));
                exercise.setShowTopic(resultSet.getString("sShowTopic"));
                exercise.setDifficulty(resultSet.getInt("tiDifficulty"));
                exercise.setSpecId(resultSet.getLong("iSpecId"));
                exercise.setStatus(resultSet.getInt("tiStatus"));
                exercise.setCreatorName(resultSet.getString("sName"));

                return exercise;
            }
        });

        log.debug("习题列表");
        return new Page<>(total, pageSize, pageNum, exerciseList);
    }



    /**
     * 验证用户是否有权限查看该习题明细
     * @param exerciseId
     * @return
     */
    public Exercise checkDetail(long exerciseId){
        String sql = "SELECT tiOpen,iCreatorId,iParentId FROM t_exercise WHERE id = :id";
        MapSqlParameterSource param = new MapSqlParameterSource().addValue("id", exerciseId);

        try {
            log.debug("验证用户是否有权限查看该习题明细");
            return namedParameterJdbcTemplate.queryForObject(sql, param, new RowMapper<Exercise>() {
                @Override
                public Exercise mapRow(ResultSet resultSet, int i) throws SQLException {
                    Exercise exercise = new Exercise();

                    exercise.setIsOpen(resultSet.getInt("tiOpen"));
                    exercise.setCreatorId(resultSet.getLong("iCreatorId"));
                    exercise.setParentId(resultSet.getLong("iParentId"));

                    return exercise;
                }
            });
        }catch (EmptyResultDataAccessException ex){
            log.debug("验证用户是否有权限查看该习题明细失败");
            log.warn("验证用户是否有权限查看该习题明细失败");
            throw new ServiceException("验证用户是否有权限查看该习题明细失败");
        }
    }

    /**
     * 获取习题明细（题干、题型、分数、专业ID、是否公开、难度、下载地址、预览地址）
     * @param exerciseId
     * @return
     * @author lyj
     * @since 2015-2-9
     */
    public Exercise getExerDetail(long exerciseId){
        //获取题干、题型、分数、专业ID、是否公开、难度、下载地址、预览地址
        String sql = "SELECT id,sTopic,tiType,iScore,iSpecId,tiOpen,tiDifficulty,sDownloadAddress,sShowAddress,sFileName,iParentId FROM t_exercise WHERE id = :id";

        MapSqlParameterSource param = new MapSqlParameterSource().addValue("id",exerciseId);

        try{
            log.debug("获取习题明细（题干、题型、分数、专业ID、是否公开、难度、下载地址、预览地址）");
            return namedParameterJdbcTemplate.queryForObject(sql, param, new RowMapper<Exercise>() {
                @Override
                public Exercise mapRow(ResultSet resultSet, int i) throws SQLException {
                    Exercise exercise = new Exercise();

                    exercise.setId(resultSet.getLong("id"));
                    exercise.setTopic(resultSet.getString("sTopic"));
                    exercise.setType(resultSet.getInt("tiType"));
                    exercise.setScore(resultSet.getLong("iScore"));
                    exercise.setSpecId(resultSet.getLong("iSpecId"));
                    exercise.setIsOpen(resultSet.getInt("tiOpen"));
                    exercise.setDifficulty(resultSet.getInt("tiDifficulty"));
                    exercise.setDownloadAddress(resultSet.getString("sDownloadAddress"));
                    exercise.setShowAddress(resultSet.getString("sShowAddress"));
                    exercise.setParentId(resultSet.getLong("iParentId"));
                    exercise.setFileName(resultSet.getString("sFileName"));

                    return exercise;
                }
            });
        }catch (EmptyResultDataAccessException ex){
            log.debug("获取习题题干、题型、分数、专业ID、是否公开、难度、下载地址、预览地址失败");
            log.warn("获取习题题干、题型、分数、专业ID、是否公开、难度、下载地址、预览地址失败：exerciseId>>>{}", exerciseId);
            throw new ServiceException("获取习题基础信息失败");
        }
    }

    /**
     * 新增习题
     * @param exercise 习题实体
     * @return int
     *
     * @author wyh
     * @since  2015-2-5
     */
    public int saveExercise(Exercise exercise) {
        String sql = "INSERT INTO t_exercise VALUES(NULL, :sTopic, :sShowTopic, :tiType, :tiDifficulty, :tiOpen, :tiStatus, :iScore, :iParentId, " +
                ":iDeptId, :iSpecId, :sDownloadAddress, :sShowAddress, :sFileName, NOW(), :iCreatorId, NOW(), 0) ";

        SqlParameterSource sps = new MapSqlParameterSource()
                .addValue("sTopic", exercise.getTopic())
                .addValue("sShowTopic", exercise.getShowTopic())
                .addValue("tiType", exercise.getType())
                .addValue("tiDifficulty", exercise.getDifficulty())
                .addValue("tiOpen", exercise.getIsOpen())
                .addValue("tiStatus", exercise.getStatus())
                .addValue("iScore", exercise.getScore())
                .addValue("iParentId", exercise.getParentId())
                .addValue("iDeptId", exercise.getDeptId())
                .addValue("iSpecId", exercise.getSpecId())
                .addValue("sDownloadAddress", exercise.getDownloadAddress())
                .addValue("sShowAddress", exercise.getShowAddress())
                .addValue("sFileName", exercise.getFileName())
                .addValue("iCreatorId", exercise.getCreatorId());

        KeyHolder key = new GeneratedKeyHolder();
        int result = namedParameterJdbcTemplate.update(sql, sps, key);

        exercise.setId(key.getKey().longValue());//设置主键

        log.debug("新增习题");
        return result;
    }

    /**
     * 新增子习题
     * @param subExercise
     * @return int
     *
     * @author wyh
     * @since 2015-2-12
     */
    public int saveSubexercise(Exercise subExercise){
        String sql = "INSERT INTO t_exercise(id, sTopic, sShowTopic, tiType, iScore, iParentId, dtCreationTime, dtAuditTime, tiStatus, sDownloadAddress) VALUES(NULL, :sTopic, :sShowTopic, :tiType, :iScore, :iParentId, NOW(), NOW(), 0, :sDownloadAddress) ";

        SqlParameterSource sps = new MapSqlParameterSource()
                .addValue("sTopic", subExercise.getTopic())
                .addValue("sShowTopic", subExercise.getShowTopic())
                .addValue("tiType", subExercise.getType())
                .addValue("iScore", subExercise.getScore())
                .addValue("iParentId", subExercise.getParentId())
                .addValue("sDownloadAddress", subExercise.getDownloadAddress());

        KeyHolder key = new GeneratedKeyHolder();
        int result = namedParameterJdbcTemplate.update(sql, sps, key);
        subExercise.setId(key.getKey().longValue());

        log.debug("新增子习题");
        return result;
    }


    /**
     * 根据用户ID获取部门ID的集合
     * @param creatorId 创建人ID
     * @return
     * @author lyj
     * @since 2015-2-13
     */
    public List<Long> getDeptIds(Long creatorId){
        String sql = "SELECT DISTINCT iDeptId FROM t_exercise WHERE iCreatorId = :iCreatorId";

        MapSqlParameterSource param = new MapSqlParameterSource().addValue("iCreatorId",creatorId);

        log.debug("根据用户ID获取部门ID的集合");
        return namedParameterJdbcTemplate.queryForList(sql, param, Long.class);

    }

    /**
     * 根据用户ID获取专业ID的集合
     * @param creatorId 创建人ID
     * @return
     * @author lyj
     * @since 2015-2-13
     */
    public List<Long> getSpecIds(Long creatorId){
        String sql = "SELECT DISTINCT iSpecId FROM t_exercise WHERE iCreatorId = :iCreatorId";

        MapSqlParameterSource param = new MapSqlParameterSource().addValue("iCreatorId",creatorId);

        log.debug("根据用户ID获取专业ID的集合");
        return namedParameterJdbcTemplate.queryForList(sql, param, Long.class);

    }


    /**
     * 根据习题ID获取附件预览信息
     * @param exerciseId  习题ID
     * @return
     * @author lyj
     * @since 2015-3-9
     */
    public Map<String, Object> getShowAnnex(long exerciseId){
        String sql = "SELECT exer.id,exer.sDownloadAddress,exer.sShowAddress,exer.sFileName,teac.sName,exer.dtCreationTime FROM t_exercise exer ,t_teacher teac WHERE exer.iCreatorId=teac.id AND exer.id = :id";
        MapSqlParameterSource param = new MapSqlParameterSource().addValue("id", exerciseId);

        try {
            return namedParameterJdbcTemplate.queryForObject(sql, param, new RowMapper<Map<String, Object>>() {
                @Override
                public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
                    Map<String, Object> map = new HashMap<String, Object>();

                    map.put("id", rs.getInt("id"));
                    map.put("downloadUrl", rs.getString("sDownloadAddress")); //下载地址
                    map.put("showUrl", rs.getString("sShowAddress")); //展示URL
                    map.put("uploader", rs.getString("sName")); //上传作者
                    map.put("date", rs.getDate("dtCreationTime")); //上传日期
                    map.put("name",rs.getString("sFileName"));

                    return map;
                }
            });
        }catch (EmptyResultDataAccessException ex){
            log.warn("获取附件预览信息失败：exerciseId->{}",exerciseId);
            throw new ServiceException("获取附件预览信息失败");
        }
    }


    /**
     * 根据习题ＩＤ删除习题
     * @param exerciseId
     * @return
     * @author lyj
     * @since 2015-3-9
     */
    public int deleteByExerciseId(long exerciseId){
        String sql = "DELETE FROM t_exercise WHERE id = :id";
        MapSqlParameterSource param = new MapSqlParameterSource().addValue("id", exerciseId);

        log.debug("根据习题ＩＤ删除习题");
        return namedParameterJdbcTemplate.update(sql, param);
    }


    /**
     * 验证习题是否被引用
     * @param exerciseId
     * @return int
     * @author lyj
     * @since 2015-4-13
     */
    public int checkExercise(final long exerciseId) {
        StringBuilder sql = new StringBuilder("SELECT COUNT(id) FROM t_test_exercise WHERE iExerciseId = :exerciseId");

        return namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource().addValue("exerciseId", exerciseId), Integer.class);
    }

    /**
     * 获取习题集合
     * @param exerciseIds 习题编号集合
     * @return List
     *
     * @author fanruiping
     * @since 2015-03-26
     */
    public List<Exercise> getByIds(List<Long> exerciseIds){
        String sql = "SELECT id,sTopic,sShowTopic,tiType,tiDifficulty,iScore FROM t_exercise WHERE id IN(:ids)";

        return namedParameterJdbcTemplate.query(sql, new MapSqlParameterSource().addValue("ids", exerciseIds), new RowMapper<Exercise>() {
            @Override
            public Exercise mapRow(ResultSet rs, int i) throws SQLException {
                Exercise exercise = new Exercise();

                exercise.setId(rs.getLong("id"));
                exercise.setTopic(rs.getString("sTopic"));
                exercise.setShowTopic(rs.getString("sShowTopic"));
                exercise.setType(rs.getInt("tiType"));
                exercise.setDifficulty(rs.getInt("tiDifficulty"));
                exercise.setScore(rs.getLong("iScore"));

                return exercise;
            }
        });
    }

    /**
     * 作业选习题列表
     * @param userId 用户编号
     * @param exerciseIds 习题编号集合
     * @param majorId 专业编号
     * @param source 来源（0：系统题库，1：我的题库）
     * @param type 题型（0：全部，1：单选，2：多选，3：判断，4：填空，5：简答，6：阅读，7：阅读多问）
     * @param pageNum 页码
     * @param pageSize 每页显示条数
     * @return Page
     *
     * @author fanruiping
     * @since 2015-03-27
     */
    public Page<Exercise> getListForTest(Long userId,List<Long>exerciseIds,Long majorId,int source,int type,int pageNum,int pageSize){
        String totalSql = "SELECT COUNT(id) FROM t_exercise";

        String dataSql = "SELECT id,sShowTopic,tiType,tiDifficulty,iScore FROM t_exercise";

        String where = " WHERE iSpecId=:specId";

        MapSqlParameterSource param = new MapSqlParameterSource().addValue("specId",majorId);

        //习题来源
        if(1 == source && 0 < userId){
            log.debug("限制创建人:1 == source && 0 < userId");

            where += " AND iCreatorId=:userId";

            param.addValue("userId",userId);
        }else if(0 == source){
            log.debug("不限制创建人:0 == source");

            where += " AND tiStatus=1";
        }

        //数据编号
        if(!exerciseIds.isEmpty()){
            log.debug("限制习题编号:!exerciseIds.isEmpty()");

            where += " AND id IN(:ids)";

            param.addValue("ids",exerciseIds);
        }

        //题型
        if(0 < type){
            log.debug("限制习题题型:0 < type");

            where += " AND tiType=:type";

            param.addValue("type",type);
        }

        //获取数据条数
        int total = namedParameterJdbcTemplate.queryForObject(totalSql+where,param,Integer.class);

        //判断数据条数
        if(total < 1){
            log.debug("习题列表数量为0:total < 1");

            return new Page<>();
        }

        List<Exercise> exerciseList = namedParameterJdbcTemplate.query(dataSql+where+getLimitStr(pageNum,pageSize), param, new RowMapper<Exercise>() {
            @Override
            public Exercise mapRow(ResultSet resultSet, int i) throws SQLException {
                Exercise exercise = new Exercise();

                exercise.setId(resultSet.getLong("id"));
                exercise.setShowTopic(resultSet.getString("sShowTopic"));
                exercise.setType(resultSet.getInt("tiType"));
                exercise.setDifficulty(resultSet.getInt("tiDifficulty"));
                exercise.setScore(resultSet.getLong("iScore"));

                return exercise;
            }
        });

        return new Page<>(total,pageSize,pageNum,exerciseList);
    }

    /**
     * 获取习题明细
     * @param id 习题编号
     * @return Exercise
     *
     * @author fanruiping
     * @since 2015-03-31
     */
    public Exercise getDetail(Long id) {
        String sql = "SELECT id,tiType,sTopic,tiDifficulty,iScore,sDownloadAddress,sShowAddress FROM t_exercise WHERE id=:id";

        try {
            return namedParameterJdbcTemplate.queryForObject(sql, new MapSqlParameterSource().addValue("id", id), new RowMapper<Exercise>() {
                @Override
                public Exercise mapRow(ResultSet rs, int i) throws SQLException {
                    Exercise exercise = new Exercise();

                    exercise.setType(rs.getInt("tiType"));
                    exercise.setScore(rs.getLong("iScore"));
                    exercise.setDifficulty(rs.getInt("tiDifficulty"));
                    exercise.setTopic(rs.getString("sTopic"));
                    exercise.setDownloadAddress(rs.getString("sDownloadAddress"));
                    exercise.setShowAddress(rs.getString("sShowAddress"));

                    return exercise;
                }
            });
        } catch (EmptyResultDataAccessException ex) {
            return null;
        }
    }
    /**
     * 查询作业的习题信息
     *
     * @param testId 作业ID
     * @param exerciseId 习题ID
     * @return TestExercise
     * 
     * @author cx
     * @since 2015/3/26
     */
    public TestExercise getTestExercise(final long testId, final long exerciseId) {
        String sql = "SELECT te.siScore,e.tiType FROM t_test_exercise te LEFT JOIN t_exercise e ON te.iExerciseId=e.id WHERE te.iTestId=:testId AND te.iExerciseId=:exerciseId";

        SqlParameterSource sps = new MapSqlParameterSource().addValue("testId", testId).addValue("exerciseId", exerciseId);

        try {
            log.debug("查询作业的习题信息");
            return namedParameterJdbcTemplate.queryForObject(sql, sps, new RowMapper<TestExercise>() {
                @Override
                public TestExercise mapRow(ResultSet resultSet, int i) throws SQLException {
                    TestExercise testExercise = new TestExercise();

                    testExercise.setTestId(testId);
                    testExercise.setExerciseId(exerciseId);
                    testExercise.setScore(resultSet.getInt("siScore"));
                    testExercise.setType(resultSet.getInt("tiType"));

                    return testExercise;
                }
            });
        } catch (EmptyResultDataAccessException ex) {
            log.debug("作业习题信息为null");
            return null;
        }
    }

    /**
     * 查询作业的习题及分数(不包含子习题)
     *
     * @param testId 作业ID
     * @return 习题集合
     *
     * @author cx
     * @since 2015/3/23
     */
    public List<TestExercise> getTestExercises(final long testId) {
        String sql = "SELECT iTestId,iExerciseId,siScore FROM t_test_exercise WHERE iTestId=:testId AND iParentId=0";

        log.debug("查询作业的习题及分数");
        return namedParameterJdbcTemplate.query(sql, new MapSqlParameterSource().addValue("testId", testId), new RowMapper<TestExercise>() {
            @Override
            public TestExercise mapRow(ResultSet rs, int rowNum) throws SQLException {
                TestExercise testExercise = new TestExercise();

                testExercise.setTestId(rs.getLong("iTestId"));
                testExercise.setExerciseId(rs.getLong("iExerciseId"));
                testExercise.setScore(rs.getInt("siScore"));

                return testExercise;
            }
        });
    }

    /**
     * 学生答题映射内部类
     * 
     * @author cx
     * @since 2015/4/2
     */
    private class StudentAnswerMapper implements RowMapper<StudentAnswer> {
        @Override
        public StudentAnswer mapRow(ResultSet resultSet, int i) throws SQLException {
            StudentAnswer studentAnswer = new StudentAnswer();

            studentAnswer.setStudentId(resultSet.getLong("iStudentId"));
            studentAnswer.setTestId(resultSet.getLong("iTestId"));
            studentAnswer.setExerciseId(resultSet.getLong("iExerciseId"));
            studentAnswer.setStatus(resultSet.getInt("tiStatus"));
            studentAnswer.setScore(resultSet.getInt("siScore"));
            studentAnswer.setAnswerContent(resultSet.getString("sAnswerContent"));
            studentAnswer.setTeacherComment(resultSet.getString("sTeacherComment"));
            studentAnswer.setParentId(resultSet.getLong("iParentId"));

            return studentAnswer;
        }
    }

    /**
     * 获取指定习题的子题
     * @param parentIds 习题编号集合
     * @return List
     *
     * @author fanruiping
     * @since 2015-04-06
     */
    public List<Exercise> getCrosshead(List<Long> parentIds){
        String sql = "SELECT id,iScore,tiType,iParentId FROM t_exercise WHERE iParentId IN(:parentId)";

        return namedParameterJdbcTemplate.query(sql, new MapSqlParameterSource().addValue("parentId", parentIds), new RowMapper<Exercise>() {
            @Override
            public Exercise mapRow(ResultSet rs, int i) throws SQLException {
                Exercise exercise = new Exercise();

                exercise.setId(rs.getLong("id"));
                exercise.setScore(rs.getLong("iScore"));
                exercise.setParentId(rs.getLong("iParentId"));
                exercise.setType(rs.getInt("tiType"));

                return exercise;
            }
        });
    }

    /**
     * 检查习题集合中是否含有主观题
     * @param exerciseIds 习题集合
     * @return boolean
     *
     * @author fanruiping
     * @since 2015-04-25
     */
    public boolean checkObjective(List<Long> exerciseIds){
        try {
            String sql = "SELECT id FROM t_exercise WHERE (tiType=5 OR tiType=6) AND id IN(:exerciseId) LIMIT 1";

            return namedParameterJdbcTemplate.queryForObject(sql, new MapSqlParameterSource().addValue("exerciseId", exerciseIds), Long.class) > 0;
        }catch (EmptyResultDataAccessException ex){
            return false;
        }
    }

    /**
     * 获取系部下习题数量
     * @param userId 用户编号
     * @param keyword 搜索关键帧
     * @return Map
     *
     * @author fanruiping
     * @since 2015-05-06
     */
    public Map<Long,Long> getTotalForDepartment(Long userId,String keyword){
        //存储结果集合
        final Map<Long,Long> result = new HashMap<>();

        String where = " WHERE iParentId=0";

        MapSqlParameterSource params = new MapSqlParameterSource();

        //限制创建人编号
        if(0 != userId){
            log.debug("限制创建人:0 != userId");

            where += " AND iCreatorId=:userId";

            params.addValue("userId",userId);
        }

        //限制匹配搜索关键字
        if(!keyword.isEmpty()){
            log.debug("限制习题题目匹配搜索关键字:!keyword.isEmpty()");

            where += " AND UPPER(sShowTopic) LIKE :keyword";

            params.addValue("keyword","%"+keyword.toUpperCase()+"%");
        }

        String sql = "SELECT iDeptId,COUNT(id) total FROM t_exercise "+where+" GROUP BY iDeptId";

        namedParameterJdbcTemplate.query(sql, params, new RowMapper<Object>() {
            @Override
            public Object mapRow(ResultSet resultSet, int i) throws SQLException {
                result.put(resultSet.getLong("iDeptId"), resultSet.getLong("total"));

                return null;
            }
        });

        return result;
    }

    /**
     * 获取专业下习题数量
     * @param userId 用户编号
     * @param keyword 搜索关键帧
     * @param specialtyIds 专业编号集合
     * @return Map
     *
     * @author fanruiping
     * @since 2015-05-06
     */
    public Map<Long,Long> getTotalForSpecialty(Long userId,String keyword,List<Long> specialtyIds){
        //存储结果集合
        final Map<Long,Long> result = new HashMap<>();

        String where = " WHERE iParentId=0";

        MapSqlParameterSource params = new MapSqlParameterSource();

        //限制创建人编号
        if(0 != userId){
            log.debug("限制创建人:0 != userId");

            where += " AND iCreatorId=:userId";

            params.addValue("userId",userId);
        }

        //限制匹配搜索关键字
        if(!keyword.isEmpty()){
            log.debug("限制习题名称匹配搜索关键字:!keyword.isEmpty()");

            where += " AND UPPER(sShowTopic) LIKE :keyword";

            params.addValue("keyword","%"+keyword.toUpperCase()+"%");
        }

        //限制专业编号集合
        if(!specialtyIds.isEmpty()){
            log.debug("限制专业编号:!specialtyIds.isEmpty()");

            where += " AND iSpecId IN(:specIds)";

            params.addValue("specIds", specialtyIds);
        }

        String sql = "SELECT iSpecId,COUNT(id) total FROM t_exercise "+where+" GROUP BY iSpecId";

        namedParameterJdbcTemplate.query(sql, params, new RowMapper<Object>() {
            @Override
            public Object mapRow(ResultSet resultSet, int i) throws SQLException {
                result.put(resultSet.getLong("iSpecId"), resultSet.getLong("total"));

                return null;
            }
        });

        return result;
    }

    /**
     * 获取指定用户创建的习题列表
     * @param userId 用户编号
     * @param departId 系部编号
     * @param specId 专业编号
     * @param keyword 搜索关键字
     * @param types 习题类型
     * @param page 页码
     * @param pageSize 分页
     * @return Page
     *
     * @author fanruiping
     * @since 2015-05-06
     */
    public Page<Exercise> getListForMine(Long userId,Long departId,Long specId,String keyword,List<Integer> types,int page,int pageSize){
        String where = " WHERE iCreatorId=:userId AND iParentId=0";

        MapSqlParameterSource params = new MapSqlParameterSource();
        params.addValue("userId", userId);

        //判断是否限制系部
        if(0 != departId){
            log.debug("限制系部编号:0 != departId");

            where += " AND iDeptId=:departId";

            params.addValue("departId", departId);
        }

        //判断是否限制专业
        if(0 != specId){
            log.debug("限制专业编号:0 != specId");

            where += " AND iSpecId=:specId";

            params.addValue("specId", specId);
        }

        //判断是否匹配搜索关键字
        if(!keyword.isEmpty()){
            log.debug("限制习题名称匹配搜索关键字");

            where += " AND UPPER(sShowTopic) LIKE :keyword";

            params.addValue("keyword", "%"+keyword.toUpperCase()+"%");
        }

        //判断是否限制题型
        if(!types.isEmpty()){
            log.debug("限制习题类型:!types.isEmpty");

            where += " AND tiType IN(:type)";

            params.addValue("type", types);
        }

        String totalSql = "SELECT COUNT(id) FROM t_exercise "+where;

        int total = namedParameterJdbcTemplate.queryForObject(totalSql,params,Integer.class);

        //判断数据条数为0
        if(total == 0){
            log.debug("习题列表为空:total == 0");

            return new Page<>();
        }

        String dataSql = "SELECT id,tiType,sShowTopic,tiDifficulty,iSpecId FROM t_exercise"+where+" ORDER BY dtCreationTime DESC"+getLimitStr(page,pageSize);

        List<Exercise> exercises = namedParameterJdbcTemplate.query(dataSql, params, new RowMapper<Exercise>() {
            @Override
            public Exercise mapRow(ResultSet resultSet, int i) throws SQLException {
                Exercise exercise = new Exercise();

                exercise.setId(resultSet.getLong("id"));
                exercise.setType(resultSet.getInt("tiType"));
                exercise.setShowTopic(resultSet.getString("sShowTopic"));
                exercise.setDifficulty(resultSet.getInt("tiDifficulty"));
                exercise.setSpecId(resultSet.getLong("iSpecId"));

                return exercise;
            }
        });

        return new Page<>(total,pageSize,page,exercises);
    }
}

